﻿using System.Data;
using MySql.Data.MySqlClient;

namespace BaiduMapPOI
{
    public class MysqlOP
    {
        public MySqlConnection conn;
        MySqlDataReader odrReader;

        public MysqlOP()
        {
            conn = new MySqlConnection(BaiduMapPOI.Properties.Settings.Default.mysqlconn);
        }

        public DataTable GetName(string lng, string lat)
        {
            DataTable dt = new DataTable();
            DataRow dr;

            string sql = "SELECT * from branches where myWithin(PolygonFromText('Point(" + lng + " " + lat + ")'),PolygonFromText(region))>0 ORDER BY id DESC limit 0,1";
            MySqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;

            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            odrReader = cmd.ExecuteReader();
            int size = odrReader.FieldCount;

            for (int i = 0; i < size; i++)
            {
                DataColumn dc;
                dc = new DataColumn(odrReader.GetName(i));
                dt.Columns.Add(dc);
            }

            while (odrReader.Read())
            {
                dr = dt.NewRow();
                for (int i = 0; i < size; i++)
                {
                    dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
                }
                dt.Rows.Add(dr);
            }

            odrReader.Close();

            return dt;
        }

        public DataTable SelectAll()
        {
            DataTable dt = new DataTable();
            DataRow dr;

            string sql = "SELECT * from branches";
            MySqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;

            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            odrReader = cmd.ExecuteReader();
            int size = odrReader.FieldCount;

            for (int i = 0; i < size; i++)
            {
                DataColumn dc;
                dc = new DataColumn(odrReader.GetName(i));
                dt.Columns.Add(dc);
            }

            while (odrReader.Read())
            {
                dr = dt.NewRow();
                for (int i = 0; i < size; i++)
                {
                    dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
                }
                dt.Rows.Add(dr);
            }

            odrReader.Close();

            return dt;
        }

        public void UpdateArea(string id, string sendArea, string areaColor)
        {
            string sql = "UPDATE branches SET sendArea = '" + sendArea + "', areaColor = '" + areaColor + "' where id = '" + id + "'";
            MySqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;

            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            odrReader = cmd.ExecuteReader();
            odrReader.Close();
        }
    }
}
